{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd ,xlwings as xw,numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "销售台账path = r'J:\\王振洋资料\\2.商贸分公司资料\\8月商贸分公司资料\\本月销售\\8月代理商家政物料销售.xlsx'\n",
    "\n",
    "基础数据path=r'J:\\王振洋资料\\2.商贸分公司资料\\t+基础数据金水分.xlsx'\n",
    "\n",
    "销售出库单p=r'J:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\销售出库单.xlsx'\n",
    "\n",
    "凭证导入导出p=r'J:\\王振洋资料\\2.商贸分公司资料\\商贸分导入模板\\凭证导入导出.xlsx'\n",
    "\n",
    "\n",
    "wb_台账=xw.Book(销售台账path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 处理台账中日期、城市列的合并单元格\n",
    "这个是在excel中进行的，在进行之后，然后再删除掉充值预存的记录，只留下销售的记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb_台账=xw.Book(销售台账path)\n",
    "# 定义sheet\n",
    "sheet=wb_台账.sheets('家政物料')\n",
    "\n",
    "\n",
    "# 去除A列合并单元格\n",
    "'''这里可能每次需要修改'''\n",
    "columnrange=sheet.range('A1:A175') \n",
    "# 遍历列，去除合并单元格\n",
    "for cell in columnrange:\n",
    "    if cell.api.MergeCells:   #如果是合并单元格\n",
    "        # 获取整个合并单元格区域的范围\n",
    "        merged_range = cell.api.MergeArea.Address\n",
    "        # 解散合并单元格\n",
    "        cell.api.UnMerge()\n",
    "        # 用合并单元格的值填充合并区域内的所有单元格\n",
    "        sheet.range(merged_range).value = cell.value\n",
    "\n",
    "\n",
    "# 去除B列合并单元格\n",
    "columnrange=sheet.range('B1:B175')  \n",
    "# 遍历列，去除合并单元格\n",
    "for cell in columnrange:\n",
    "    if cell.api.MergeCells:   #如果是合并单元格\n",
    "        # 获取整个合并单元格区域的范围\n",
    "        merged_range = cell.api.MergeArea.Address\n",
    "        # 解散合并单元格\n",
    "        cell.api.UnMerge()\n",
    "        # 用合并单元格的值填充合并区域内的所有单元格\n",
    "        sheet.range(merged_range).value = cell.value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 获取数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "台账data = wb_台账.sheets('家政物料').range('a1').expand('table').options(pd.DataFrame,index=False).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 添加一列标识，用来标识原来是合并单元格的若干行，用于计算汇总看总价计算是否正确。\n",
    "# 定义一个累加值，如果城市<>None累加值增加1，如果是None累加值不变。 合并表示这里是用来做后面的groupby的，一个合并表示就是一个组，一张凭证。\n",
    "累加值=1\n",
    "比较日期=台账data.loc[0,'时间']\n",
    "比较城市=台账data.loc[0,'城市']\n",
    "\n",
    "# 1.1 生成一个合并标识列，用来做groupby。\n",
    "for index,row in 台账data.iterrows():\n",
    "    if row['城市']!=比较城市 or row['时间']!=比较日期:\n",
    "        累加值+=1\n",
    "        比较日期=row['时间']\n",
    "        比较城市=row['城市']\n",
    "    else:\n",
    "        累加值+=0\n",
    "    台账data.at[index,'合并标识'] = 累加值\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>物料名称</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>金额</th>\n",
       "      <th>优惠金额</th>\n",
       "      <th>总价</th>\n",
       "      <th>合计金额</th>\n",
       "      <th>支付方式</th>\n",
       "      <th>支付时间</th>\n",
       "      <th>实收</th>\n",
       "      <th>合并标识</th>\n",
       "      <th>new_总价</th>\n",
       "      <th>验证</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>蓝导蒸汽机（到家）</td>\n",
       "      <td>折扣</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-89.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-89.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           时间  城市       物料名称  数量  单价    金额  优惠金额    总价  合计金额  支付方式 支付时间  实收  \\\n",
       "11 2024-08-02  郑州  蓝导蒸汽机（到家）  折扣 NaN -89.5   NaN -89.5   NaN  None  NaT NaN   \n",
       "\n",
       "    合并标识 new_总价   验证  \n",
       "11   1.0    NaN  NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "验证=(\n",
    "    台账data.assign(new_总价=lambda x:x['数量'] * x['单价'])\n",
    "    .assign(验证=lambda x:x['new_总价']-x['总价'])\n",
    "    .query('验证!=0')\n",
    ")\n",
    "验证  # 如果出来的验证没有数据就是正确的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取出需要的列\n",
    "台账data=台账data.loc[:,['合并标识','时间','城市','物料名称','数量','单价','总价']]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>合并标识</th>\n",
       "      <th>时间</th>\n",
       "      <th>城市</th>\n",
       "      <th>物料名称</th>\n",
       "      <th>数量</th>\n",
       "      <th>单价</th>\n",
       "      <th>总价</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>油烟机粉（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>厨房清洁剂（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>蓝导蒸汽机（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>780.0</td>\n",
       "      <td>780.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>伸缩杆（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2024-08-02</td>\n",
       "      <td>郑州</td>\n",
       "      <td>地板擦（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-08-28</td>\n",
       "      <td>郑州</td>\n",
       "      <td>七色保洁布（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-08-28</td>\n",
       "      <td>郑州</td>\n",
       "      <td>小黄刮（到家）</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-08-28</td>\n",
       "      <td>郑州</td>\n",
       "      <td>喷瓶（到家）</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>11.0</td>\n",
       "      <td>2024-08-28</td>\n",
       "      <td>郑州</td>\n",
       "      <td>地板擦（到家）</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>180</th>\n",
       "      <td>12.0</td>\n",
       "      <td>2024-08-27</td>\n",
       "      <td>濮阳</td>\n",
       "      <td>保洁背包（到家）</td>\n",
       "      <td>10.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>600.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>181 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     合并标识         时间  城市       物料名称    数量     单价     总价\n",
       "0     1.0 2024-08-02  郑州   油烟机粉（到家）   1.0    7.0    7.0\n",
       "1     1.0 2024-08-02  郑州  厨房清洁剂（到家）   1.0    7.0    7.0\n",
       "2     1.0 2024-08-02  郑州  蓝导蒸汽机（到家）   1.0  780.0  780.0\n",
       "3     1.0 2024-08-02  郑州    伸缩杆（到家）   1.0   15.0   15.0\n",
       "4     1.0 2024-08-02  郑州    地板擦（到家）   1.0   18.0   18.0\n",
       "..    ...        ...  ..        ...   ...    ...    ...\n",
       "176  11.0 2024-08-28  郑州  七色保洁布（到家）   1.0   30.0   30.0\n",
       "177  11.0 2024-08-28  郑州    小黄刮（到家）   2.0    3.0    6.0\n",
       "178  11.0 2024-08-28  郑州     喷瓶（到家）   3.0    3.0    9.0\n",
       "179  11.0 2024-08-28  郑州    地板擦（到家）   1.0   18.0   18.0\n",
       "180  12.0 2024-08-27  濮阳   保洁背包（到家）  10.0   60.0  600.0\n",
       "\n",
       "[181 rows x 7 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "台账data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 编码匹配"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "wb基础数据=xw.Book(基础数据path)\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]\n",
    "往来单位档案=wb基础数据.sheets(\"往来单位匹配汇总-家政代驾\").range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(台账data,存货档案,left_on='物料名称',right_on='存货名称',how='left')\n",
    "合并=pd.merge(合并,往来单位档案,left_on='城市',right_on='城市',how='left')\n",
    "合并['部门编码']='03'\n",
    "合并['部门']='招商部'\n",
    "合并['时间']=合并['时间'].dt.strftime('%Y-%m-%d')  #修改时间格式\n",
    "#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将数据写入编码匹配中间表\n",
    "\n",
    "sheetname = '编码匹配中间表'\n",
    "    \n",
    "wb_台账.sheets(sheetname).cells.clear_contents()\n",
    "wb_台账.sheets(sheetname).cells.number_format = '@'\n",
    "wb_台账.sheets(sheetname).range('a1').value=合并 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 模版数据源 与 销售出库单、凭证导入导出模版"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 检查是否有销售出库单和凭证导入导出，如果没有，则打开并新建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheetname = wb_台账.sheet_names\n",
    "\n",
    "if '销售出库单' not in sheetname:\n",
    "    出库单=xw.Book(销售出库单p)\n",
    "    出库单.sheets['销售出库单'].copy(after=wb_台账.sheets[wb_台账.sheets.count-1])\n",
    "   \n",
    "else:\n",
    "    None\n",
    "\n",
    "if '凭证导入导出' not in sheetname:\n",
    "    凭证导入导出=xw.Book(凭证导入导出p)\n",
    "    凭证导入导出.sheets('凭证导入导出').copy(after=wb_台账.sheets[wb_台账.sheets.count-1])\n",
    "else:\n",
    "    None\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到销售出库单\n",
    "\n",
    "1.修改往来单位名称为 往来单位\n",
    "2.手动添加备注一列\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源=wb_台账.sheets('模版数据源').range('a1').expand('table').options(pd.DataFrame).value\n",
    "凭证模版=wb_台账.sheets('凭证导入导出')\n",
    "出库单模版=wb_台账.sheets(\"销售出库单\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "出库单模版.range('a5:af10000').clear_contents()\n",
    "出库单模版.range(\"g3:j3\").value=数据源.loc[:,['往来单位编码','往来单位','部门编码','部门']].values\n",
    "出库单模版.range('s3:t3').value=数据源.loc[:,['仓库编码','仓库名称']].values\n",
    "出库单模版.range('w3:x3').value=数据源.loc[:,['存货编码','存货名称']].values\n",
    "出库单模版.range('z3:aa3').value=数据源.loc[:,['计量单位','数量']].values\n",
    "出库单模版.range('r3').options(transpose=True).value=数据源['备注'].values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到凭证导入导出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取df\n",
    "数据源=wb_台账.sheets('模版数据源').range('a1').expand().options(pd.DataFrame).value\n",
    "凭证模版=wb_台账.sheets('凭证导入导出')\n",
    "出库单模版=wb_台账.sheets(\"销售出库单\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 按组添加往来科目和销项税科目(df):\n",
    "    df=df.reset_index(drop=True)\n",
    "    # 应收账款的数据\n",
    "    应收金额=df['合计金额'].sum()\n",
    "    往来单位编码=df.loc[0,'往来单位编码'] #读取往来单位编码数据\n",
    "    往来单位名称=df.loc[0,'往来单位'] #读取往来单位的名称\n",
    "    data={'科目编码':'2203','科目':'预收账款','币种':'人民币','借贷方向':'借方','本币':应收金额,'往来单位编码':往来单位编码,'往来单位':往来单位名称,'部门编码':'','部门':''}\n",
    "    应收数据=pd.DataFrame(data,index=[0])\n",
    "    \n",
    "    # 收入的数据\n",
    "    收入数据=df.loc[:,['科目编码','科目','不含税金额','往来单位编码','往来单位','部门编码','部门',]]\n",
    "    收入数据['币种']='人民币'\n",
    "    收入数据['借贷方向']='贷方'\n",
    "    收入数据['往来单位编码']=''\n",
    "    收入数据['往来单位']=''\n",
    "    收入数据['本币']=收入数据['不含税金额']\n",
    "    收入数据=收入数据.loc[:,['科目编码','科目','币种','借贷方向','本币','往来单位编码','往来单位','部门编码','部门']]\n",
    "\n",
    "    # 销项税的数据\n",
    "    销项金额=df['税额'].sum()\n",
    "    data={'科目编码':'22210106','科目':'','币种':'人民币','借贷方向':'贷方','本币':销项金额,'往来单位编码':'','往来单位':'','部门编码':'','部门':''}\n",
    "    销项数据=pd.DataFrame(data,index=[1])\n",
    "\n",
    "    合并=pd.concat([应收数据,收入数据,销项数据])\n",
    "    合并['备注']=df.loc[0,'备注']\n",
    "    return 合并   \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证导入导出=数据源.groupby(by=['合并标识']).apply(按组添加往来科目和销项税科目).reset_index()\n",
    "凭证导入导出['原币']=凭证导入导出['本币']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从凭证导入导出将数据写入到《凭证导入导出》\n",
    "凭证模版.range('a4:ab10000').clear_contents()\n",
    "凭证模版.range('h2:j2').value=凭证导入导出.loc[:,['科目编码','科目','币种']].values  \n",
    "凭证模版.range('m2:o2').value=凭证导入导出.loc[:,['借贷方向','原币','本币']].values\n",
    "凭证模版.range('y2:ab2').value=凭证导入导出.loc[:,['往来单位编码','往来单位','部门编码','部门']].values\n",
    "凭证模版.range('g2').options(transpose=True).value= 凭证导入导出.loc[:,'备注'].values #摘要\n",
    "凭证模版.range('c2').options(transpose=True).value= 凭证导入导出.loc[:,'合并标识'].values #凭证号\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
